PostgreSQL 15ではpublicスキーマへの書き込みが制限されます

#PostgreSQL

#SQL

quiver

2022.07.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

PostgreSQLではデータベースを作成すると、デフォルトで public スキーマが作成され、任意のユーザーがこのスキーマにオブジェクトを作成できました。

CVE-2018-1058 でpublicスキーマのこの仕様とsearch_pathを使ったトロイの木馬攻撃の脆弱性(仕様の潜在リスク)が報告されました。

この攻撃から守るために、以下のような方法が推奨されています。

public スキーマの CREATE 権限を REVOKE
ユーザーごとにスキーマを割り振る
search_path に public スキーマが含まれないように調整

PostgreSQL 15からは、1つ目の回避策がデフォルトで有効になり、データベースのオーナーだけがpublicスキーマにオブジェクトを作成できるようになります。

Remove PUBLIC creation permission on the public schema (Noah Misch)

This is a change in the default for newly-created databases in existing clusters and for new clusters; USAGE permissions on the public schema has not been changed. Databases restored from previous Postgres releases will be restored with their current permissions. Users wishing to have the old permissions on new objects will need to grant CREATE permission for PUBLIC on the public schema; this change can be made on template1 to cause all new databases to have these permissions.

PostgreSQL: Documentation: 15: E.1. Release 15

PostgreSQL 15 のかなり大きなバージョン非互換。publicスキーマに対するCREATE,USAGE権限が無くなります。 https://t.co/OetbIJBdnC

— Noriyoshi Shinoda (@nori_shinoda) September 10, 2021

この仕様変更を動作確認します。

PostgreSQLのデータベース・スキーマ・テーブルの関係

最初にPostgreSQLのデータベースとスキーマとテーブルの関係をおさらいします。

initdb を実行すると、データベースクラスターが作成され、デフォルトで

template0
template1
postgres

の3つのデータベースが作成されます。

データベースは名前空間を分割でき、この粒度をスキーマと呼びます。

各データベースにはデフォルトで public スキーマが作成されます。

ユーザーはテーブルを特定のスキーマに作成します。

CVE-2018-1058 とそのリスク軽減方法

この public スキーマは /tmp ディレクトリのようなもので、任意のユーザーがオブジェクト(例えばテーブル)を作成できます。

オブジェクトを検索するスキーマを指定する search_path にpublic スキーマが含まえていると、異なるスキーマに同じ名前のオブジェクトを作成可能で、「トロイの木馬」攻撃のリスクがあります(CVE-2018-1058)。

public スキーマをセキュアに運用するための手順は公式ドキュメントで「セキュアなスキーマの使用パターン」として解説されており

public スキーマの CREATE 権限を REVOKE
ユーザーごとにスキーマを割り振る
search_path に public スキーマが含まれないように調整

などが推奨されます。

PostgreSQL 15ではデータベースのオーナーだけがpublicスキーマに書き込める

PostgreSQL 15では、public スキーマの権限は REVOKE CREATE ON SCHEMA public FROM PUBLIC を実行した状態となり、データベースのオーナー(pg_database_owner)だけがオブジェクトを作成できます。

この動作を確認します。

別オーナーの public スキーマにはオブジェクトを作成できない

ユーザー john を作成し、デフォルトの postgres データベースの public スキーマにテーブルを作成してみましょう。

postgres=# create user john with password 'YOURPASSWORD';
CREATE ROLE

postgres=# \c postgres john
Password for user john:
You are now connected to database "postgres" as user "john".

postgres=> create table public.a(a int);
ERROR:  permission denied for schema public
LINE 1: create table public.a(a int);

permission denied が発生し、テーブルを作成できませんでした。

データベースオーナーのスキーマにはオブジェクトを作成できる

セキュアなスキーマの使用パターンでは、public スキーマを使用せず、ユーザーごとにスキーマを払い出すことが推奨されています。

ユーザー john 向けのスキーマを作成します。

postgres=# CREATE SCHEMA AUTHORIZATION john;
CREATE SCHEMA

postgres=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 john   | john
(2 rows)

このスキーマには、テーブルを作成できます。

postgres=> create table john.a(a int);
CREATE TABLE

postgres=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 john   | a    | table | john
(3 rows)

自分がオーナーの public スキーマにはオブジェクトを作成できる

次に、自身が所有するデータベース(test)の public スキーマにテーブルを作成してみましょう。

postgres=# create database test with owner john;
CREATE DATABASE
postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 test      | john     | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
(4 rows)

postgres=> \c test john
You are now connected to database "test" as user "john".

この public スキーマにはテーブルを作成できます。

test=> create table public.a(a int);
CREATE TABLE

test=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | a    | table | john
(1 rows)